package com.deng.maintenance.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.deng.common.SQLHelper;
import com.deng.common.vo.OrganizationVo;
import com.deng.maintenance.dao.IVoteValue;
import com.deng.maintenance.vo.JiangChengDictVo;
import com.deng.maintenance.vo.VoteValueMingdanVo;
import com.deng.maintenance.vo.VoteValueVo;
import com.deng.maintenance.vo.VoteValueVo2;
import com.deng.maintenance.vo.VoteVsOrganizationVo;
import com.deng.util.DataFormater;
import com.deng.util.DateTools;

public class VoteValueImpl extends SQLHelper implements IVoteValue {

	public int deleteData(VoteValueVo vo) {
		try{
			System.out.println("删除:   "+vo);
			Connection conn = getConnect();
			String sql = "delete vote_value " +
					"	where vote_id = "+vo.vote_id+" and organization_id = "+ vo.organization_id +
					"	and frq_code = '"+vo.frq_code.trim()+"' and stat_time = '"+DateTools.dtsWithTime(vo.stat_time)+"'";
			int x = updateData(conn,sql);
			freeConnection(conn);
			return x;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;
	}

	public int insertData(VoteValueVo vo) {
		try{
			String sql = "insert into vote_value" +
			"	(vote_id,organization_id,score,frq_code,create_time,modify_time,stat_time)" +
			"	values("+vo.vote_id+","+vo.organization_id+","+vo.score+"," +
			"	'"+vo.frq_code.trim()+"','"+DateTools.dtsWithTime(new Date())+"'," +
			"	'"+DateTools.dtsWithTime(new Date())+"','"+DateTools.dtsWithTime(vo.stat_time)+"')";
			Connection conn = getConnect();
			int x =  updateData(conn,sql);
			freeConnection(conn);
			return x;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;
	}

	public void insertOrDeleteData(VoteValueVo vo) {
		// TODO Auto-generated method stub

	}

	public void searchAllData(VoteValueVo vo) {
		// TODO Auto-generated method stub

	}

	public List<VoteValueVo> searchAllDataByOrganizationIdAndDate(int organization_id,String frq_code,String year,int sel) {
		try{
			String sql = "";
			if(frq_code.equals("0001")){//年
				sql = "select vote_vs_organization.vote_id,vote_dict.name,vote_vs_organization.organization_id," +
						"	vote_vs_organization.frq_code,vote_value.score,'"+year+"-1-1' as stat_time,vote_value.valid,vote_value.valid_time,vote_value.valid_user_code " +
						"	from vote_value right join vote_vs_organization " +
						"	on vote_value.vote_id = vote_vs_organization.vote_id  " +
						"	and vote_vs_organization.organization_id = vote_value.organization_id"+
						"	and cast(vote_value.frq_code as nvarchar) = cast('"+frq_code+"' as nvarchar)"+//SQL2000
//						"	and vote_value.frq_code = '"+frq_code+"'"+SQL2005
						"	and year(vote_value.stat_time) = " +year+
						"	and month(vote_value.stat_time) = " +1+
						"	and day(vote_value.stat_time) = " +1+
						"	left join vote_dict" +
						"	on vote_vs_organization.vote_id = vote_dict.id" +
						"	where vote_vs_organization.organization_id = "+organization_id +
						"	and vote_vs_organization.frq_code = '"+frq_code+"'";
			}else if(frq_code.equals("0002")){//半年
				if(sel == 0){//上半年
					sql = "select vote_vs_organization.vote_id,vote_dict.name,vote_vs_organization.organization_id," +
					"	vote_vs_organization.frq_code,vote_value.score,'"+year+"-1-1' as stat_time ,vote_value.valid,vote_value.valid_time,vote_value.valid_user_code" +
					"	from vote_value right join vote_vs_organization " +
					"	on vote_value.vote_id = vote_vs_organization.vote_id  " +
					"	and vote_vs_organization.organization_id = vote_value.organization_id"+
					"	and cast(vote_value.frq_code as nvarchar) = cast('"+frq_code+"' as nvarchar)"+//SQL2000
//					"	and vote_value.frq_code = '"+frq_code+"'"+SQL2005
					"	and year(vote_value.stat_time) = " +year+
					"	and month(vote_value.stat_time) = 1"+
					"	left join vote_dict" +
					"	on vote_vs_organization.vote_id = vote_dict.id" +
					"	where vote_vs_organization.organization_id = "+organization_id +
					"	and vote_vs_organization.frq_code = '"+frq_code+"'";
				}else{//下半年
					sql = "select vote_vs_organization.vote_id,vote_dict.name,vote_vs_organization.organization_id," +
					"	vote_vs_organization.frq_code,vote_value.score,'"+year+"-7-1' as stat_time ,vote_value.valid,vote_value.valid_time,vote_value.valid_user_code" +
					"	from vote_value right join vote_vs_organization " +
					"	on vote_value.vote_id = vote_vs_organization.vote_id  " +
					"	and cast(vote_value.frq_code as nvarchar) = cast('"+frq_code+"' as nvarchar)"+//SQL2000
//					"	and vote_value.frq_code = '"+frq_code+"'"+SQL2005
					"	and vote_vs_organization.organization_id = vote_value.organization_id"+
					"	and year(vote_value.stat_time) = " +year+
					"	and month(vote_value.stat_time) = 7"+
					"	left join vote_dict" +
					"	on vote_vs_organization.vote_id = vote_dict.id" +
					"	where vote_vs_organization.organization_id = "+organization_id +
					"	and vote_vs_organization.frq_code = '"+frq_code+"'";
				}
			}else if(frq_code.equals("0003")){//季度
				if(sel == 0){//1
					sql = "select vote_vs_organization.vote_id,vote_dict.name,vote_vs_organization.organization_id," +
					"	vote_vs_organization.frq_code,vote_value.score,'"+year+"-1-1' as stat_time ,vote_value.valid,vote_value.valid_time,vote_value.valid_user_code" +
					"	from vote_value right join vote_vs_organization " +
					"	on vote_value.vote_id = vote_vs_organization.vote_id  " +
					"	and vote_vs_organization.organization_id = vote_value.organization_id"+
					"	and cast(vote_value.frq_code as nvarchar) = cast('"+frq_code+"' as nvarchar)"+//SQL2000
//					"	and vote_value.frq_code = '"+frq_code+"'"+SQL2005
					"	and year(vote_value.stat_time) = " +year+
					"	and month(vote_value.stat_time) = 1"+
					"	left join vote_dict" +
					"	on vote_vs_organization.vote_id = vote_dict.id" +
					"	where vote_vs_organization.organization_id = "+organization_id +
					"	and vote_vs_organization.frq_code = '"+frq_code+"'";
				}else if(sel == 1){//2
					sql = "select vote_vs_organization.vote_id,vote_dict.name,vote_vs_organization.organization_id," +
					"	vote_vs_organization.frq_code,vote_value.score,'"+year+"-4-1' as stat_time ,vote_value.valid,vote_value.valid_time,vote_value.valid_user_code" +
					"	from vote_value right join vote_vs_organization " +
					"	on vote_value.vote_id = vote_vs_organization.vote_id  " +
					"	and vote_vs_organization.organization_id = vote_value.organization_id"+
					"	and cast(vote_value.frq_code as nvarchar) = cast('"+frq_code+"' as nvarchar)"+//SQL2000
//					"	and vote_value.frq_code = '"+frq_code+"'"+SQL2005
					"	and year(vote_value.stat_time) = " +year+
					"	and month(vote_value.stat_time) = 4"+
					"	left join vote_dict" +
					"	on vote_vs_organization.vote_id = vote_dict.id" +
					"	where vote_vs_organization.organization_id = "+organization_id +
					"	and vote_vs_organization.frq_code = '"+frq_code+"'";
				}else if(sel == 2){//3
					sql = "select vote_vs_organization.vote_id,vote_dict.name,vote_vs_organization.organization_id," +
					"	vote_vs_organization.frq_code,vote_value.score,'"+year+"-7-1' as stat_time ,vote_value.valid,vote_value.valid_time,vote_value.valid_user_code" +
					"	from vote_value right join vote_vs_organization " +
					"	on vote_value.vote_id = vote_vs_organization.vote_id  " +
					"	and vote_vs_organization.organization_id = vote_value.organization_id"+
					"	and cast(vote_value.frq_code as nvarchar) = cast('"+frq_code+"' as nvarchar)"+//SQL2000
//					"	and vote_value.frq_code = '"+frq_code+"'"+SQL2005
					"	and year(vote_value.stat_time) = " +year+
					"	and month(vote_value.stat_time) = 7"+
					"	left join vote_dict" +
					"	on vote_vs_organization.vote_id = vote_dict.id" +
					"	where vote_vs_organization.organization_id = "+organization_id +
					"	and vote_vs_organization.frq_code = '"+frq_code+"'";
				}else{//4
					sql = "select vote_vs_organization.vote_id,vote_dict.name,vote_vs_organization.organization_id," +
					"	vote_vs_organization.frq_code,vote_value.score,'"+year+"-10-1' as stat_time,vote_value.valid,vote_value.valid_time,vote_value.valid_user_code " +
					"	from vote_value right join vote_vs_organization " +
					"	on vote_value.vote_id = vote_vs_organization.vote_id  " +
					"	and vote_vs_organization.organization_id = vote_value.organization_id"+
					"	and cast(vote_value.frq_code as nvarchar) = cast('"+frq_code+"' as nvarchar)"+//SQL2000
//					"	and vote_value.frq_code = '"+frq_code+"'"+SQL2005
					"	and year(vote_value.stat_time) = " +year+
					"	and month(vote_value.stat_time) = 10"+
					"	left join vote_dict" +
					"	on vote_vs_organization.vote_id = vote_dict.id" +
					"	where vote_vs_organization.organization_id = "+organization_id +
					"	and vote_vs_organization.frq_code = '"+frq_code+"'";
				}
			}else{//月
				sql = "select vote_vs_organization.vote_id,vote_dict.name,vote_vs_organization.organization_id," +
				"	vote_vs_organization.frq_code,vote_value.score,'"+year+"-"+(sel+1)+"-1' as stat_time ,vote_value.valid,vote_value.valid_time,vote_value.valid_user_code" +
				"	from vote_value right join vote_vs_organization " +
				"	on vote_value.vote_id = vote_vs_organization.vote_id  " +
				"	and vote_vs_organization.organization_id = vote_value.organization_id"+
				"	and cast(vote_value.frq_code as nvarchar) = cast('"+frq_code+"' as nvarchar)"+//SQL2000
//				"	and vote_value.frq_code = '"+frq_code+"'"+SQL2005
				"	and year(vote_value.stat_time) = " +year+
				"	and month(vote_value.stat_time) = " +(sel+1)+
				"	left join vote_dict" +
				"	on vote_vs_organization.vote_id = vote_dict.id" +
				"	where vote_vs_organization.organization_id = "+organization_id +
				"	and vote_vs_organization.frq_code = '"+frq_code+"'";
			}
			
			List<VoteValueVo> l = new ArrayList<VoteValueVo>();
			Connection conn = getConnect();
			ResultSet rs = queryData(conn,sql);
			while(rs.next()){
				VoteValueVo vo = new VoteValueVo();
				vo.organization_id = rs.getInt("organization_id");
				vo.score = rs.getBigDecimal("score")!=null?rs.getBigDecimal("score").toString():"";
				vo.stat_time = rs.getDate("stat_time");
				if(vo.stat_time != null){
					vo.stat_time_str = DateTools.dtsWithTime(vo.stat_time);
				}
				if(rs.getDate("valid_time") != null){
					vo.valid_time = DateTools.dtsJustDate(rs.getDate("valid_time"));
				}
				vo.valid_user_code = rs.getString("valid_user_code");
				vo.valid = rs.getInt("valid");
				vo.frq_code = rs.getString("frq_code");
				vo.vote_id = rs.getInt("vote_id");
				vo.vote_name = rs.getString("name");
				l.add(vo);
			}
			freeConnection(conn);
			return l;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;

	}

	public List<VoteValueVo> searchAllDataByVoteIdAndDate(String frq_code,String year,int sel) {
		try{
			String sql = "";
			String stat_time_str = "";
			if(frq_code.equals("0001")){//年
				stat_time_str = year+"-1-1";
				sql = "select vote_id," +
						" (select name from VOTE_DICT where id = vote_id) as vote_name," +
						" (select function_detail from VOTE_DICT where id = vote_id) as function_detail," +
						" '"+stat_time_str+"' as stat_time," +
						" (select score from VOTE_VALUE where stat_time = '" +stat_time_str+"'"+
						" and VOTE_VALUE.vote_id = vote_vs_organization.vote_id" +
						" and VOTE_VALUE.organization_id = vote_vs_organization.organization_id" +
						" ) as value," +
						" (select xishu from VOTE_VALUE where stat_time = '" +stat_time_str+"'"+
						" and VOTE_VALUE.vote_id = vote_vs_organization.vote_id" +
						" and VOTE_VALUE.organization_id = vote_vs_organization.organization_id" +
						" ) as xishu," +
						" (select valid from VOTE_VALUE where stat_time = '" +stat_time_str+"'"+
						" and VOTE_VALUE.vote_id = vote_vs_organization.vote_id" +
						" and VOTE_VALUE.organization_id = vote_vs_organization.organization_id" +
						" ) as valid," +
						" (select valid_time from VOTE_VALUE where stat_time = '" +stat_time_str+"'"+
						" and VOTE_VALUE.vote_id = vote_vs_organization.vote_id" +
						" and VOTE_VALUE.organization_id = vote_vs_organization.organization_id" +
						" ) as valid_time," +
						" (select valid_user_code from VOTE_VALUE where stat_time = '" +stat_time_str+"'"+
						" and VOTE_VALUE.vote_id = vote_vs_organization.vote_id" +
						" and VOTE_VALUE.organization_id = vote_vs_organization.organization_id" +
						" ) as valid_user_code," +
						" organization_id," +
						" (select newCode  from t_sys_stat_department where id = vote_vs_organization.organization_id) as newCode," +
						" (select (select name from dict_duty_type where id = duty_id)+'-'+name " +
						" from t_sys_stat_department where id = vote_vs_organization.organization_id) as oName," +
						" frq_code from vote_vs_organization" +
						" where frq_code = '0001'" +
						" order by vote_name,oName";				
//				sql = "select vote_vs_organization.vote_id,vote_dict.name,vote_vs_organization.organization_id," +
//						"	(select (select name from dict_duty_type where id = duty_id)+'-'+name from t_sys_stat_department where id = vote_vs_organization.organization_id) as oName," +
//						"	vote_vs_organization.frq_code,vote_value.score,'"+year+"-1-1' as stat_time,vote_value.valid,vote_value.valid_time,vote_value.valid_user_code,vote_value.xishu " +
//						"	from vote_value right join vote_vs_organization " +
//						"	on vote_value.vote_id = vote_vs_organization.vote_id  " +
//						"	and vote_vs_organization.organization_id = vote_value.organization_id"+
//						"	and cast(vote_value.frq_code as nvarchar) = cast('"+frq_code+"' as nvarchar)"+//SQL2000
////						"	and vote_value.frq_code = '"+frq_code+"'"+SQL2005
//						"	and year(vote_value.stat_time) = " +year+
//						"	and month(vote_value.stat_time) = " +1+
//						"	and day(vote_value.stat_time) = " +1+
//						"	left join vote_dict" +
//						"	on vote_vs_organization.vote_id = vote_dict.id" +
//						"	where vote_vs_organization.vote_id = "+vote_id +
//						"	and vote_vs_organization.frq_code = '"+frq_code+"'"+
//						"	order by oName";
			}else if(frq_code.equals("0002")){//半年
				if(sel == 0){//上半年					
					stat_time_str = year+"-1-1";
					sql = "select vote_id," +
							" (select name from VOTE_DICT where id = vote_id) as vote_name," +
							" (select function_detail from VOTE_DICT where id = vote_id) as function_detail," +
							" '"+stat_time_str+"' as stat_time," +
							" (select score from VOTE_VALUE where stat_time = '" +stat_time_str+"'"+
							" and VOTE_VALUE.vote_id = vote_vs_organization.vote_id" +
							" and VOTE_VALUE.organization_id = vote_vs_organization.organization_id" +
							" ) as value," +
							" (select xishu from VOTE_VALUE where stat_time = '" +stat_time_str+"'"+
							" and VOTE_VALUE.vote_id = vote_vs_organization.vote_id" +
							" and VOTE_VALUE.organization_id = vote_vs_organization.organization_id" +
							" ) as xishu," +
							" (select valid from VOTE_VALUE where stat_time = '" +stat_time_str+"'"+
							" and VOTE_VALUE.vote_id = vote_vs_organization.vote_id" +
							" and VOTE_VALUE.organization_id = vote_vs_organization.organization_id" +
							" ) as valid," +
							" (select valid_time from VOTE_VALUE where stat_time = '" +stat_time_str+"'"+
							" and VOTE_VALUE.vote_id = vote_vs_organization.vote_id" +
							" and VOTE_VALUE.organization_id = vote_vs_organization.organization_id" +
							" ) as valid_time," +
							" (select valid_user_code from VOTE_VALUE where stat_time = '" +stat_time_str+"'"+
							" and VOTE_VALUE.vote_id = vote_vs_organization.vote_id" +
							" and VOTE_VALUE.organization_id = vote_vs_organization.organization_id" +
							" ) as valid_user_code," +
							" organization_id," +
							" (select newCode  from t_sys_stat_department where id = vote_vs_organization.organization_id) as newCode," +
							" (select (select name from dict_duty_type where id = duty_id)+'-'+name " +
							" from t_sys_stat_department where id = vote_vs_organization.organization_id) as oName," +
							" frq_code from vote_vs_organization" +
							" where frq_code = '0002'" +
							" order by vote_name,oName";	
//					sql = "select vote_vs_organization.vote_id,vote_dict.name,vote_vs_organization.organization_id," +
//					"	(select (select name from dict_duty_type where id = duty_id)+'-'+name from t_sys_stat_department where id = vote_vs_organization.organization_id) as oName," +
//					"	vote_vs_organization.frq_code,vote_value.score,'"+year+"-1-1' as stat_time ,vote_value.valid,vote_value.valid_time,vote_value.valid_user_code,vote_value.xishu " +
//					"	from vote_value right join vote_vs_organization " +
//					"	on vote_value.vote_id = vote_vs_organization.vote_id  " +
//					"	and vote_vs_organization.organization_id = vote_value.organization_id"+
//					"	and cast(vote_value.frq_code as nvarchar) = cast('"+frq_code+"' as nvarchar)"+//SQL2000
////					"	and vote_value.frq_code = '"+frq_code+"'"+SQL2005
//					"	and year(vote_value.stat_time) = " +year+
//					"	and month(vote_value.stat_time) = 1"+
//					"	left join vote_dict" +
//					"	on vote_vs_organization.vote_id = vote_dict.id" +
//					"	where vote_vs_organization.vote_id = "+vote_id +
//					"	and vote_vs_organization.frq_code = '"+frq_code+"'"+
//					"	order by oName";
				}else{//下半年
					stat_time_str = year+"-7-1";
					sql = "select vote_id," +
							" (select name from VOTE_DICT where id = vote_id) as vote_name," +
							" (select function_detail from VOTE_DICT where id = vote_id) as function_detail," +
							" '"+stat_time_str+"' as stat_time," +
							" (select score from VOTE_VALUE where stat_time = '" +stat_time_str+"'"+
							" and VOTE_VALUE.vote_id = vote_vs_organization.vote_id" +
							" and VOTE_VALUE.organization_id = vote_vs_organization.organization_id" +
							" ) as value," +
							" (select xishu from VOTE_VALUE where stat_time = '" +stat_time_str+"'"+
							" and VOTE_VALUE.vote_id = vote_vs_organization.vote_id" +
							" and VOTE_VALUE.organization_id = vote_vs_organization.organization_id" +
							" ) as xishu," +
							" (select valid from VOTE_VALUE where stat_time = '" +stat_time_str+"'"+
							" and VOTE_VALUE.vote_id = vote_vs_organization.vote_id" +
							" and VOTE_VALUE.organization_id = vote_vs_organization.organization_id" +
							" ) as valid," +
							" (select valid_time from VOTE_VALUE where stat_time = '" +stat_time_str+"'"+
							" and VOTE_VALUE.vote_id = vote_vs_organization.vote_id" +
							" and VOTE_VALUE.organization_id = vote_vs_organization.organization_id" +
							" ) as valid_time," +
							" (select valid_user_code from VOTE_VALUE where stat_time = '" +stat_time_str+"'"+
							" and VOTE_VALUE.vote_id = vote_vs_organization.vote_id" +
							" and VOTE_VALUE.organization_id = vote_vs_organization.organization_id" +
							" ) as valid_user_code," +
							" organization_id," +
							" (select newCode  from t_sys_stat_department where id = vote_vs_organization.organization_id) as newCode," +
							" (select (select name from dict_duty_type where id = duty_id)+'-'+name " +
							" from t_sys_stat_department where id = vote_vs_organization.organization_id) as oName," +
							" frq_code from vote_vs_organization" +
							" where frq_code = '0002'" +
							" order by vote_name,oName";	
//					sql = "select vote_vs_organization.vote_id,vote_dict.name,vote_vs_organization.organization_id," +
//					"	(select (select name from dict_duty_type where id = duty_id)+'-'+name from t_sys_stat_department where id = vote_vs_organization.organization_id) as oName," +
//					"	vote_vs_organization.frq_code,vote_value.score,'"+year+"-7-1' as stat_time ,vote_value.valid,vote_value.valid_time,vote_value.valid_user_code,vote_value.xishu " +
//					"	from vote_value right join vote_vs_organization " +
//					"	on vote_value.vote_id = vote_vs_organization.vote_id  " +
//					"	and cast(vote_value.frq_code as nvarchar) = cast('"+frq_code+"' as nvarchar)"+//SQL2000
////					"	and vote_value.frq_code = '"+frq_code+"'"+SQL2005
//					"	and vote_vs_organization.organization_id = vote_value.organization_id"+
//					"	and year(vote_value.stat_time) = " +year+
//					"	and month(vote_value.stat_time) = 7"+
//					"	left join vote_dict" +
//					"	on vote_vs_organization.vote_id = vote_dict.id" +
//					"	where vote_vs_organization.vote_id = "+vote_id +
//					"	and vote_vs_organization.frq_code = '"+frq_code+"'"+
//					"	order by oName";
				}
			}else if(frq_code.equals("0003")){//季度
				if(sel == 0){//1
					stat_time_str = year+"-1-1";
					sql = "select vote_id," +
							" (select name from VOTE_DICT where id = vote_id) as vote_name," +
							" (select function_detail from VOTE_DICT where id = vote_id) as function_detail," +
							" '"+stat_time_str+"' as stat_time," +
							" (select score from VOTE_VALUE where stat_time = '" +stat_time_str+"'"+
							" and VOTE_VALUE.vote_id = vote_vs_organization.vote_id" +
							" and VOTE_VALUE.organization_id = vote_vs_organization.organization_id" +
							" ) as value," +
							" (select xishu from VOTE_VALUE where stat_time = '" +stat_time_str+"'"+
							" and VOTE_VALUE.vote_id = vote_vs_organization.vote_id" +
							" and VOTE_VALUE.organization_id = vote_vs_organization.organization_id" +
							" ) as xishu," +
							" (select valid from VOTE_VALUE where stat_time = '" +stat_time_str+"'"+
							" and VOTE_VALUE.vote_id = vote_vs_organization.vote_id" +
							" and VOTE_VALUE.organization_id = vote_vs_organization.organization_id" +
							" ) as valid," +
							" (select valid_time from VOTE_VALUE where stat_time = '" +stat_time_str+"'"+
							" and VOTE_VALUE.vote_id = vote_vs_organization.vote_id" +
							" and VOTE_VALUE.organization_id = vote_vs_organization.organization_id" +
							" ) as valid_time," +
							" (select valid_user_code from VOTE_VALUE where stat_time = '" +stat_time_str+"'"+
							" and VOTE_VALUE.vote_id = vote_vs_organization.vote_id" +
							" and VOTE_VALUE.organization_id = vote_vs_organization.organization_id" +
							" ) as valid_user_code," +
							" organization_id," +
							" (select newCode  from t_sys_stat_department where id = vote_vs_organization.organization_id) as newCode," +
							" (select (select name from dict_duty_type where id = duty_id)+'-'+name " +
							" from t_sys_stat_department where id = vote_vs_organization.organization_id) as oName," +
							" frq_code from vote_vs_organization" +
							" where frq_code = '0003'" +
							" order by vote_name,oName";	
//					sql = "select vote_vs_organization.vote_id,vote_dict.name,vote_vs_organization.organization_id," +
//					"	(select (select name from dict_duty_type where id = duty_id)+'-'+name from t_sys_stat_department where id = vote_vs_organization.organization_id) as oName," +
//					"	vote_vs_organization.frq_code,vote_value.score,'"+year+"-1-1' as stat_time ,vote_value.valid,vote_value.valid_time,vote_value.valid_user_code,vote_value.xishu " +
//					"	from vote_value right join vote_vs_organization " +
//					"	on vote_value.vote_id = vote_vs_organization.vote_id  " +
//					"	and vote_vs_organization.organization_id = vote_value.organization_id"+
//					"	and cast(vote_value.frq_code as nvarchar) = cast('"+frq_code+"' as nvarchar)"+//SQL2000
////					"	and vote_value.frq_code = '"+frq_code+"'"+SQL2005
//					"	and year(vote_value.stat_time) = " +year+
//					"	and month(vote_value.stat_time) = 1"+
//					"	left join vote_dict" +
//					"	on vote_vs_organization.vote_id = vote_dict.id" +
//					"	where vote_vs_organization.vote_id = "+vote_id +
//					"	and vote_vs_organization.frq_code = '"+frq_code+"'"+
//					"	order by oName";
				}else if(sel == 1){//2
					stat_time_str = year+"-4-1";
					sql = "select vote_id," +
							" (select name from VOTE_DICT where id = vote_id) as vote_name," +
							" (select function_detail from VOTE_DICT where id = vote_id) as function_detail," +
							" '"+stat_time_str+"' as stat_time," +
							" (select score from VOTE_VALUE where stat_time = '" +stat_time_str+"'"+
							" and VOTE_VALUE.vote_id = vote_vs_organization.vote_id" +
							" and VOTE_VALUE.organization_id = vote_vs_organization.organization_id" +
							" ) as value," +
							" (select xishu from VOTE_VALUE where stat_time = '" +stat_time_str+"'"+
							" and VOTE_VALUE.vote_id = vote_vs_organization.vote_id" +
							" and VOTE_VALUE.organization_id = vote_vs_organization.organization_id" +
							" ) as xishu," +
							" (select valid from VOTE_VALUE where stat_time = '" +stat_time_str+"'"+
							" and VOTE_VALUE.vote_id = vote_vs_organization.vote_id" +
							" and VOTE_VALUE.organization_id = vote_vs_organization.organization_id" +
							" ) as valid," +
							" (select valid_time from VOTE_VALUE where stat_time = '" +stat_time_str+"'"+
							" and VOTE_VALUE.vote_id = vote_vs_organization.vote_id" +
							" and VOTE_VALUE.organization_id = vote_vs_organization.organization_id" +
							" ) as valid_time," +
							" (select valid_user_code from VOTE_VALUE where stat_time = '" +stat_time_str+"'"+
							" and VOTE_VALUE.vote_id = vote_vs_organization.vote_id" +
							" and VOTE_VALUE.organization_id = vote_vs_organization.organization_id" +
							" ) as valid_user_code," +
							" organization_id," +
							" (select newCode  from t_sys_stat_department where id = vote_vs_organization.organization_id) as newCode," +
							" (select (select name from dict_duty_type where id = duty_id)+'-'+name " +
							" from t_sys_stat_department where id = vote_vs_organization.organization_id) as oName," +
							" frq_code from vote_vs_organization" +
							" where frq_code = '0003'" +
							" order by vote_name,oName";	
//					sql = "select vote_vs_organization.vote_id,vote_dict.name,vote_vs_organization.organization_id," +
//					"	(select (select name from dict_duty_type where id = duty_id)+'-'+name from t_sys_stat_department where id = vote_vs_organization.organization_id) as oName," +
//					"	vote_vs_organization.frq_code,vote_value.score,'"+year+"-4-1' as stat_time ,vote_value.valid,vote_value.valid_time,vote_value.valid_user_code,vote_value.xishu " +
//					"	from vote_value right join vote_vs_organization " +
//					"	on vote_value.vote_id = vote_vs_organization.vote_id  " +
//					"	and vote_vs_organization.organization_id = vote_value.organization_id"+
//					"	and cast(vote_value.frq_code as nvarchar) = cast('"+frq_code+"' as nvarchar)"+//SQL2000
////					"	and vote_value.frq_code = '"+frq_code+"'"+SQL2005
//					"	and year(vote_value.stat_time) = " +year+
//					"	and month(vote_value.stat_time) = 4"+
//					"	left join vote_dict" +
//					"	on vote_vs_organization.vote_id = vote_dict.id" +
//					"	where vote_vs_organization.vote_id = "+vote_id +
//					"	and vote_vs_organization.frq_code = '"+frq_code+"'"+
//					"	order by oName";
				}else if(sel == 2){//3
					stat_time_str = year+"-7-1";
					sql = "select vote_id," +
							" (select name from VOTE_DICT where id = vote_id) as vote_name," +
							" (select function_detail from VOTE_DICT where id = vote_id) as function_detail," +
							" '"+stat_time_str+"' as stat_time," +
							" (select score from VOTE_VALUE where stat_time = '" +stat_time_str+"'"+
							" and VOTE_VALUE.vote_id = vote_vs_organization.vote_id" +
							" and VOTE_VALUE.organization_id = vote_vs_organization.organization_id" +
							" ) as value," +
							" (select xishu from VOTE_VALUE where stat_time = '" +stat_time_str+"'"+
							" and VOTE_VALUE.vote_id = vote_vs_organization.vote_id" +
							" and VOTE_VALUE.organization_id = vote_vs_organization.organization_id" +
							" ) as xishu," +
							" (select valid from VOTE_VALUE where stat_time = '" +stat_time_str+"'"+
							" and VOTE_VALUE.vote_id = vote_vs_organization.vote_id" +
							" and VOTE_VALUE.organization_id = vote_vs_organization.organization_id" +
							" ) as valid," +
							" (select valid_time from VOTE_VALUE where stat_time = '" +stat_time_str+"'"+
							" and VOTE_VALUE.vote_id = vote_vs_organization.vote_id" +
							" and VOTE_VALUE.organization_id = vote_vs_organization.organization_id" +
							" ) as valid_time," +
							" (select valid_user_code from VOTE_VALUE where stat_time = '" +stat_time_str+"'"+
							" and VOTE_VALUE.vote_id = vote_vs_organization.vote_id" +
							" and VOTE_VALUE.organization_id = vote_vs_organization.organization_id" +
							" ) as valid_user_code," +
							" organization_id," +
							" (select newCode  from t_sys_stat_department where id = vote_vs_organization.organization_id) as newCode," +
							" (select (select name from dict_duty_type where id = duty_id)+'-'+name " +
							" from t_sys_stat_department where id = vote_vs_organization.organization_id) as oName," +
							" frq_code from vote_vs_organization" +
							" where frq_code = '0003'" +
							" order by vote_name,oName";	
//					sql = "select vote_vs_organization.vote_id,vote_dict.name,vote_vs_organization.organization_id," +
//					"	(select (select name from dict_duty_type where id = duty_id)+'-'+name from t_sys_stat_department where id = vote_vs_organization.organization_id) as oName," +
//					"	vote_vs_organization.frq_code,vote_value.score,'"+year+"-7-1' as stat_time ,vote_value.valid,vote_value.valid_time,vote_value.valid_user_code,vote_value.xishu " +
//					"	from vote_value right join vote_vs_organization " +
//					"	on vote_value.vote_id = vote_vs_organization.vote_id  " +
//					"	and vote_vs_organization.organization_id = vote_value.organization_id"+
//					"	and cast(vote_value.frq_code as nvarchar) = cast('"+frq_code+"' as nvarchar)"+//SQL2000
////					"	and vote_value.frq_code = '"+frq_code+"'"+SQL2005
//					"	and year(vote_value.stat_time) = " +year+
//					"	and month(vote_value.stat_time) = 7"+
//					"	left join vote_dict" +
//					"	on vote_vs_organization.vote_id = vote_dict.id" +
//					"	where vote_vs_organization.vote_id = "+vote_id +
//					"	and vote_vs_organization.frq_code = '"+frq_code+"'"+
//					"	order by oName";
				}else{//4
					stat_time_str = year+"-10-1";
					sql = "select vote_id," +
							" (select name from VOTE_DICT where id = vote_id) as vote_name," +
							" (select function_detail from VOTE_DICT where id = vote_id) as function_detail," +
							" '"+stat_time_str+"' as stat_time," +
							" (select score from VOTE_VALUE where stat_time = '" +stat_time_str+"'"+
							" and VOTE_VALUE.vote_id = vote_vs_organization.vote_id" +
							" and VOTE_VALUE.organization_id = vote_vs_organization.organization_id" +
							" ) as value," +
							" (select xishu from VOTE_VALUE where stat_time = '" +stat_time_str+"'"+
							" and VOTE_VALUE.vote_id = vote_vs_organization.vote_id" +
							" and VOTE_VALUE.organization_id = vote_vs_organization.organization_id" +
							" ) as xishu," +
							" (select valid from VOTE_VALUE where stat_time = '" +stat_time_str+"'"+
							" and VOTE_VALUE.vote_id = vote_vs_organization.vote_id" +
							" and VOTE_VALUE.organization_id = vote_vs_organization.organization_id" +
							" ) as valid," +
							" (select valid_time from VOTE_VALUE where stat_time = '" +stat_time_str+"'"+
							" and VOTE_VALUE.vote_id = vote_vs_organization.vote_id" +
							" and VOTE_VALUE.organization_id = vote_vs_organization.organization_id" +
							" ) as valid_time," +
							" (select valid_user_code from VOTE_VALUE where stat_time = '" +stat_time_str+"'"+
							" and VOTE_VALUE.vote_id = vote_vs_organization.vote_id" +
							" and VOTE_VALUE.organization_id = vote_vs_organization.organization_id" +
							" ) as valid_user_code," +
							" organization_id," +
							" (select newCode  from t_sys_stat_department where id = vote_vs_organization.organization_id) as newCode," +
							" (select (select name from dict_duty_type where id = duty_id)+'-'+name " +
							" from t_sys_stat_department where id = vote_vs_organization.organization_id) as oName," +
							" frq_code from vote_vs_organization" +
							" where frq_code = '0003'" +
							" order by vote_name,oName";	
//					sql = "select vote_vs_organization.vote_id,vote_dict.name,vote_vs_organization.organization_id," +
//					"	(select (select name from dict_duty_type where id = duty_id)+'-'+name from t_sys_stat_department where id = vote_vs_organization.organization_id) as oName," +
//					"	vote_vs_organization.frq_code,vote_value.score,'"+year+"-10-1' as stat_time,vote_value.valid,vote_value.valid_time,vote_value.valid_user_code,vote_value.xishu " +
//					"	from vote_value right join vote_vs_organization " +
//					"	on vote_value.vote_id = vote_vs_organization.vote_id  " +
//					"	and vote_vs_organization.organization_id = vote_value.organization_id"+
//					"	and cast(vote_value.frq_code as nvarchar) = cast('"+frq_code+"' as nvarchar)"+//SQL2000
////					"	and vote_value.frq_code = '"+frq_code+"'"+SQL2005
//					"	and year(vote_value.stat_time) = " +year+
//					"	and month(vote_value.stat_time) = 10"+
//					"	left join vote_dict" +
//					"	on vote_vs_organization.vote_id = vote_dict.id" +
//					"	where vote_vs_organization.vote_id = "+vote_id +
//					"	and vote_vs_organization.frq_code = '"+frq_code+"'"+
//					"	order by oName";
				}
			}else{//月
				stat_time_str = year+"-"+(sel+1)+"-1";
				sql = "select vote_id," +
						" (select name from VOTE_DICT where id = vote_id) as vote_name," +
						" (select function_detail from VOTE_DICT where id = vote_id) as function_detail," +
						" '"+stat_time_str+"' as stat_time," +
						" (select score from VOTE_VALUE where stat_time = '" +stat_time_str+"'"+
						" and VOTE_VALUE.vote_id = vote_vs_organization.vote_id" +
						" and VOTE_VALUE.organization_id = vote_vs_organization.organization_id" +
						" ) as value," +
						" (select xishu from VOTE_VALUE where stat_time = '" +stat_time_str+"'"+
						" and VOTE_VALUE.vote_id = vote_vs_organization.vote_id" +
						" and VOTE_VALUE.organization_id = vote_vs_organization.organization_id" +
						" ) as xishu," +
						" (select valid from VOTE_VALUE where stat_time = '" +stat_time_str+"'"+
						" and VOTE_VALUE.vote_id = vote_vs_organization.vote_id" +
						" and VOTE_VALUE.organization_id = vote_vs_organization.organization_id" +
						" ) as valid," +
						" (select valid_time from VOTE_VALUE where stat_time = '" +stat_time_str+"'"+
						" and VOTE_VALUE.vote_id = vote_vs_organization.vote_id" +
						" and VOTE_VALUE.organization_id = vote_vs_organization.organization_id" +
						" ) as valid_time," +
						" (select valid_user_code from VOTE_VALUE where stat_time = '" +stat_time_str+"'"+
						" and VOTE_VALUE.vote_id = vote_vs_organization.vote_id" +
						" and VOTE_VALUE.organization_id = vote_vs_organization.organization_id" +
						" ) as valid_user_code," +
						" organization_id," +
						" (select newCode  from t_sys_stat_department where id = vote_vs_organization.organization_id) as newCode," +
						" (select (select name from dict_duty_type where id = duty_id)+'-'+name " +
						" from t_sys_stat_department where id = vote_vs_organization.organization_id) as oName," +						
						" frq_code from vote_vs_organization" +
						" where frq_code = '0004'" +
						" order by vote_name,oName";	
//				sql = "select vote_vs_organization.vote_id,vote_dict.name,vote_vs_organization.organization_id," +
//				"	(select (select name from dict_duty_type where id = duty_id)+'-'+name from t_sys_stat_department where id = vote_vs_organization.organization_id) as oName," +
//				"	vote_vs_organization.frq_code,vote_value.score,cast('"+year+"-"+(sel+1)+"-1' as datetime) as stat_time ,vote_value.valid,vote_value.valid_time,vote_value.valid_user_code,vote_value.xishu " +
//				"	from vote_value right join vote_vs_organization " +
//				"	on vote_value.vote_id = vote_vs_organization.vote_id  " +
//				"	and vote_vs_organization.organization_id = vote_value.organization_id"+
//				"	and cast(vote_value.frq_code as nvarchar) = cast('"+frq_code+"' as nvarchar)"+//SQL2000
////				"	and vote_value.frq_code = '"+frq_code+"'"+SQL2005
//				"	and year(vote_value.stat_time) = " +year+
//				"	and month(vote_value.stat_time) = " +(sel+1)+
//				"	left join vote_dict" +
//				"	on vote_vs_organization.vote_id = vote_dict.id" +
//				"	where vote_vs_organization.vote_id = "+vote_id +
//				"	and vote_vs_organization.frq_code = '"+frq_code+"'" +
//				"	order by oName";
			}
			
			List<VoteValueVo> l = new ArrayList<VoteValueVo>();
			Connection conn = getConnect();
			ResultSet rs = queryData(conn,sql);
			while(rs.next()){
				VoteValueVo vo = new VoteValueVo();
				vo.organization_id = rs.getInt("organization_id");
				vo.newCode = rs.getString("newCode");
				vo.organization_name = rs.getString("oName");
				vo.score = DataFormater.sqlBigDecimal(rs.getBigDecimal("value")).toString();
				vo.stat_time_str = stat_time_str;
				vo.stat_time = DateTools.stdJustDate(stat_time_str);
				if(rs.getDate("valid_time") != null){
					vo.valid_time = DateTools.dtsJustDate(rs.getDate("valid_time"));
				}
				if(rs.getBigDecimal("xishu") == null)
				{
					vo.xishu = "1";
				}
				else
				{
					vo.xishu = rs.getBigDecimal("xishu").toString();
				}
				vo.valid_user_code = rs.getString("valid_user_code");
				vo.valid = rs.getInt("valid");
				vo.frq_code = rs.getString("frq_code");
				vo.vote_id = rs.getInt("vote_id");
				vo.vote_name = rs.getString("vote_name");
				l.add(vo);
//				System.out.println(vo.organization_name+"    "+vo.score);
			}
			
			for(VoteValueVo vo:l)
			{
				sql = "select * from VOTE_VALUE_MINGDAN " +
						" where stat_time = '"+vo.stat_time_str+"'" +
						" and vote_id = "+vo.vote_id+
						" and organization_id ="+vo.organization_id;
				rs = queryData(conn, sql);
				vo.mingdan_arr = new ArrayList();
				while(rs.next())
				{
					VoteValueMingdanVo mdVo = new VoteValueMingdanVo();
					mdVo.gonghao = rs.getString("gonghao");
					mdVo.name = rs.getString("name");
					mdVo.vote_id = vo.vote_id;
					mdVo.stat_time = vo.stat_time_str;
					mdVo.xishu = rs.getBigDecimal("xishu").toString();
					mdVo.organization_id = vo.organization_id;
					vo.mingdan_arr.add(mdVo);
				}
			}
			freeConnection(conn);
			return l;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;

	}
	public int updateValue(VoteValueVo vo) {
		try{
			System.out.println("修改  vo	"+vo);
			Connection conn = getConnect();
			String sql = "update vote_value " +
					"	set score = " + vo.score+", modify_time = '"+DateTools.dtsWithTime(new Date())+"' " +
					"	where vote_id = "+vo.vote_id+" and organization_id = "+ vo.organization_id +
					"	and frq_code = '"+vo.frq_code.trim()+"' and stat_time = '"+DateTools.dtsWithTime(vo.stat_time)+"'";
			int x = updateData(conn,sql);
			freeConnection(conn);
			return x;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;
	}

	public List<VoteValueVo2> searchAllDataByDispenseAndDate(
			int dispense_id,int vote_id, String frq_code, String year, int sel) {
		try{
			String sql = "";
			if(frq_code.equals("0001")){//年
				sql = "select "+vote_id+" as vote_id,score,'"+frq_code+"' as frq_code," +
						"	a.employee_id,a.name,a.dispense_organization_id," +
						"	a.valid,'"+year+"-1-1' as stat_time  " +
						"	from VOTE_VALUE2 " +
						"	right join " +
						"	( select employee_id,name,gongzuojixiaohesuandanyuan,valid " +
						"	from t_stat_employee_month " +
						"	where year(stat_time) = "+year+
						"	and gongzuojixiaohesuandanyuan = "+dispense_id+
						" 	group by employee_id,name,gongzuojixiaohesuandanyuan,valid" +
						"	) as a " +
						"	on VOTE_VALUE2.employee_id = a.employee_id " +
						"	and VOTE_VALUE2.frq_code = '"+frq_code+"'"+
						"	and VOTE_VALUE2.dispense_id = a.gongzuojixiaohesuandanyuan" +
						"	and year(VOTE_VALUE2.stat_time) = "+year+
						"	and month(stat_time) = 1 " +
						"	and day(stat_time) = 1"+
						"	and VOTE_VALUE2.vote_id = "+vote_id+
						"	and VOTE_VALUE2.frq_code = '"+frq_code+"' order by name";
			}else if(frq_code.equals("0002")){//半年
				if(sel == 0){//上半年
					sql = "select "+vote_id+" as vote_id,score,'"+frq_code+"' as frq_code," +
						"	a.employee_id,a.name,a.gongzuojixiaohesuandanyuan,a.valid," +
						"	'"+year+"-1-1' as stat_time" +
						"	from VOTE_VALUE2 " +
						"	right join " +
						"	( select employee_id,name,gongzuojixiaohesuandanyuan,valid " +
						"	from t_stat_employee_month " +
						"	where year(stat_time) = "+year+
						"	and month(stat_time) >= 1 and month(stat_time) <= 6"+
						"	and gongzuojixiaohesuandanyuan = "+dispense_id+
						" 	group by employee_id,name,gongzuojixiaohesuandanyuan,valid" +
						"	) as a " +
						"	on VOTE_VALUE2.employee_id = a.employee_id " +
						"	and VOTE_VALUE2.frq_code = '"+frq_code+"'"+
						"	and VOTE_VALUE2.dispense_id = a.gongzuojixiaohesuandanyuan" +
						"	and year(VOTE_VALUE2.stat_time) = "+year+
						"	and month(stat_time) = 1 " +
						"	and day(stat_time) = 1"+
						"	and VOTE_VALUE2.vote_id = "+vote_id+
						"	and VOTE_VALUE2.frq_code = '"+frq_code+"' order by name";
				}else{//下半年
					sql = "select "+vote_id+" as vote_id,score,'"+frq_code+"' as frq_code," +
						"	a.employee_id,a.name,a.gongzuojixiaohesuandanyuan,a.valid,'"+year+"-7-1' as stat_time" +
						"	from VOTE_VALUE2 " +
						"	right join " +
						"	( select employee_id,name,gongzuojixiaohesuandanyuan,valid " +
						"	from t_stat_employee_month " +
						"	where year(stat_time) = "+year+
						"	and month(stat_time) >= 7 and month(stat_time) <= 12"+
						"	and gongzuojixiaohesuandanyuan = "+dispense_id+
						" 	group by employee_id,name,gongzuojixiaohesuandanyuan,valid" +
						"	) as a " +
						"	on VOTE_VALUE2.employee_id = a.employee_id " +
						"	and VOTE_VALUE2.dispense_id = a.gongzuojixiaohesuandanyuan" +
						"	and VOTE_VALUE2.frq_code = '"+frq_code+"'"+
						"	and year(VOTE_VALUE2.stat_time) = "+year+
						"	and month(VOTE_VALUE2.stat_time) = 7 " +
						"	and day(VOTE_VALUE2.stat_time) = 1"+
						"	and VOTE_VALUE2.vote_id = "+vote_id+
						"	and VOTE_VALUE2.frq_code = '"+frq_code+"' order by name";
				}
			}else if(frq_code.equals("0003")){//季度
				if(sel == 0){//1
					sql = "select "+vote_id+" as vote_id,score,'"+frq_code+"' as frq_code," +
						"	a.employee_id,a.name,a.gongzuojixiaohesuandanyuan,a.valid,'"+year+"-1-1' as stat_time" +
						"	from VOTE_VALUE2 " +
						"	right join " +
						"	( select employee_id,name,gongzuojixiaohesuandanyuan,valid " +
						"	from t_stat_employee_month " +
						"	where year(stat_time) = "+year+
						"	and month(stat_time) >= 1 and month(stat_time) <= 3"+
						"	and gongzuojixiaohesuandanyuan = "+dispense_id+
						" 	group by employee_id,name,gongzuojixiaohesuandanyuan,valid" +
						"	) as a " +
						"	on VOTE_VALUE2.employee_id = a.employee_id " +
						"	and VOTE_VALUE2.frq_code = '"+frq_code+"'"+
						"	and VOTE_VALUE2.dispense_id = a.gongzuojixiaohesuandanyuan" +
						"	and year(VOTE_VALUE2.stat_time) = "+year+
						"	and month(VOTE_VALUE2.stat_time) = 1 " +
						"	and day(VOTE_VALUE2.stat_time) = 1"+
						"	and VOTE_VALUE2.vote_id = "+vote_id+
						"	and VOTE_VALUE2.frq_code = '"+frq_code+"' order by name";
				}else if(sel == 1){//2
					sql = "select "+vote_id+" as vote_id,score,'"+frq_code+"' as frq_code," +
						"	a.employee_id,a.name,a.gongzuojixiaohesuandanyuan,a.valid,'"+year+"-4-1' as stat_time" +
						"	from VOTE_VALUE2 " +
						"	right join " +
						"	( select employee_id,name,gongzuojixiaohesuandanyuan,valid " +
						"	from t_stat_employee_month " +
						"	where year(stat_time) = "+year+
						"	and month(stat_time) >= 4 and month(stat_time) <= 6"+
						"	and gongzuojixiaohesuandanyuan = "+dispense_id+
						" 	group by employee_id,name,gongzuojixiaohesuandanyuan,valid" +
						"	) as a " +
						"	on VOTE_VALUE2.employee_id = a.employee_id " +
						"	and VOTE_VALUE2.frq_code = '"+frq_code+"'"+
						"	and VOTE_VALUE2.dispense_id = a.gongzuojixiaohesuandanyuan" +
						"	and year(VOTE_VALUE2.stat_time) = "+year+
						"	and month(VOTE_VALUE2.stat_time) = 4 " +
						"	and day(VOTE_VALUE2.stat_time) = 1"+
						"	and VOTE_VALUE2.vote_id = "+vote_id+
						"	and VOTE_VALUE2.frq_code = '"+frq_code+"' order by name";
				}else if(sel == 2){//3
					sql = "select "+vote_id+" as vote_id,score,'"+frq_code+"' as frq_code," +
						"	a.employee_id,a.name,a.gongzuojixiaohesuandanyuan,a.valid,'"+year+"-7-1' as stat_time" +
						"	from VOTE_VALUE2 " +
						"	right join " +
						"	( select employee_id,name,gongzuojixiaohesuandanyuan,valid " +
						"	from t_stat_employee_month " +
						"	where year(stat_time) = "+year+
						"	and month(stat_time) >= 7 and month(stat_time) <= 9"+
						"	and gongzuojixiaohesuandanyuan = "+dispense_id+
						" 	group by employee_id,name,gongzuojixiaohesuandanyuan,valid" +
						"	) as a " +
						"	on VOTE_VALUE2.employee_id = a.employee_id " +
						"	and VOTE_VALUE2.frq_code = '"+frq_code+"'"+
						"	and VOTE_VALUE2.dispense_id = a.gongzuojixiaohesuandanyuan" +
						"	and year(VOTE_VALUE2.stat_time) = "+year+
						"	and month(VOTE_VALUE2.stat_time) = 7 " +
						"	and day(VOTE_VALUE2.stat_time) = 1"+
						"	and VOTE_VALUE2.vote_id = "+vote_id+
						"	and VOTE_VALUE2.frq_code = '"+frq_code+"' order by name";
				}else{//4
					sql = "select "+vote_id+" as vote_id,score,'"+frq_code+"' as frq_code," +
						"	a.employee_id,a.name,a.gongzuojixiaohesuandanyuan,a.valid,'"+year+"-10-1' as stat_time" +
						"	from VOTE_VALUE2 " +
						"	right join " +
						"	( select employee_id,name,gongzuojixiaohesuandanyuan,valid " +
						"	from t_stat_employee_month " +
						"	where year(stat_time) = "+year+
						"	and month(stat_time) >= 10 and month(stat_time) <= 12"+
						"	and gongzuojixiaohesuandanyuan = "+dispense_id+
						" 	group by employee_id,name,gongzuojixiaohesuandanyuan,valid" +
						"	) as a " +
						"	on VOTE_VALUE2.employee_id = a.employee_id " +
						"	and VOTE_VALUE2.frq_code = '"+frq_code+"'"+
						"	and VOTE_VALUE2.dispense_id = a.gongzuojixiaohesuandanyuan" +
						"	and year(VOTE_VALUE2.stat_time) = "+year+
						"	and month(VOTE_VALUE2.stat_time) = 10 " +
						"	and day(VOTE_VALUE2.stat_time) = 1"+
						"	and VOTE_VALUE2.vote_id = "+vote_id+
						"	and VOTE_VALUE2.frq_code = '"+frq_code+"' order by name";
				}
			}else{//月
				sql = "select "+vote_id+" as vote_id,score,'"+frq_code+"' as frq_code," +
					"	a.employee_id,a.name,a.gongzuojixiaohesuandanyuan,a.valid,'"+year+"-"+(sel+1)+"-1' as stat_time" +
					"	from VOTE_VALUE2 " +
					"	right join " +
					"	( select employee_id,name,gongzuojixiaohesuandanyuan,valid " +
					"	from t_stat_employee_month " +
					"	where year(stat_time) = "+year+
					"	and month(stat_time) = "+(sel+1)+
					"	and gongzuojixiaohesuandanyuan = "+dispense_id+
					" 	group by employee_id,name,gongzuojixiaohesuandanyuan,valid" +
					"	) as a " +
					"	on VOTE_VALUE2.employee_id = a.employee_id " +
					"	and VOTE_VALUE2.frq_code = '"+frq_code+"'"+
					"	and VOTE_VALUE2.dispense_id = a.gongzuojixiaohesuandanyuan" +
					"	and year(VOTE_VALUE2.stat_time) = "+year+
					"	and month(VOTE_VALUE2.stat_time) = " +(sel+1)+
					"	and day(VOTE_VALUE2.stat_time) = 1"+
					"	and VOTE_VALUE2.vote_id = "+vote_id+
					"	and VOTE_VALUE2.frq_code = '"+frq_code+"' order by name";
			}
			
			List<VoteValueVo2> l = new ArrayList<VoteValueVo2>();
			Connection conn = getConnect();
			ResultSet rs = queryData(conn,sql);
			while(rs.next()){
				VoteValueVo2 vo = new VoteValueVo2();
				vo.vote_id = rs.getInt("vote_id");
				vo.organization_id = rs.getInt("gongzuojixiaohesuandanyuan");
				vo.score = rs.getBigDecimal("score")!=null?rs.getBigDecimal("score").toString():"";
				vo.employee_id = rs.getInt("employee_id");
				vo.employee_name = rs.getString("name");
				vo.stat_time = rs.getDate("stat_time");
				if(vo.stat_time != null){
					vo.stat_time_str = DateTools.dtsWithTime(vo.stat_time);
				}
				vo.frq_code = rs.getString("frq_code");
				vo.valid = rs.getInt("valid");
				l.add(vo);
			}
			freeConnection(conn);
			return l;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}

	public int deleteData2(VoteValueVo2 vo) {
		try{
			System.out.println("删除:   "+vo);
			Connection conn = getConnect();
			String sql = "delete VOTE_VALUE2 " +
					"	where vote_id = "+vo.vote_id+"" +
					"	and dispense_id = "+ vo.organization_id +
					"	and employee_id = "+vo.employee_id+
					"	and frq_code = '"+vo.frq_code.trim()+"'" +
					"	and stat_time = '"+DateTools.dtsWithTime(vo.stat_time)+"'";
			int x = updateData(conn,sql);
			freeConnection(conn);
			return x;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;
	}

	public int insertData2(VoteValueVo2 vo) {
		try{
			String sql = "insert into VOTE_VALUE2" +
			"	(vote_id,dispense_id,score,frq_code,create_time,modify_time,stat_time,employee_id)" +
			"	values("+vo.vote_id+","+vo.organization_id+","+vo.score+"," +
			"	'"+vo.frq_code.trim()+"','"+DateTools.dtsWithTime(new Date())+"'," +
			"	'"+DateTools.dtsWithTime(new Date())+"','"+DateTools.dtsWithTime(vo.stat_time)+"'," +
			"	"+vo.employee_id+")";
			Connection conn = getConnect();
			int x =  updateData(conn,sql);
			freeConnection(conn);
			return x;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;
	}

	public int updateValue2(VoteValueVo2 vo) {
		try{
			System.out.println("修改  vo	"+vo);
			Connection conn = getConnect();
			String sql = "update VOTE_VALUE2 " +
					"	set score = " + vo.score+", " +
					"	modify_time = '"+DateTools.dtsWithTime(new Date())+"' " +
					"	where vote_id = "+vo.vote_id+
					"	and dispense_id = "+ vo.organization_id +
					"	and employee_id = "+vo.employee_id+
					"	and frq_code = '"+vo.frq_code.trim()+"'" +
					"	and stat_time = '"+DateTools.dtsWithTime(vo.stat_time)+"'";
			int x = updateData(conn,sql);
			freeConnection(conn);
			return x;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;
	}

	public void validData(VoteValueVo vo) {
		try{
			System.out.println("修改  vo	"+vo);
			Connection conn = getConnect();
			String sql = "update VOTE_VALUE " +
					"	set valid = 1, " +
					"	valid_time = '"+DateTools.dtsWithTime(new Date())+"', " +
					"	valid_user_code = '"+vo.valid_user_code+"' " +
					"	where vote_id = "+vo.vote_id+
					"	and organization_id = "+ vo.organization_id +
					"	and frq_code = '"+vo.frq_code.trim()+"'" +
					"	and stat_time = '"+DateTools.dtsWithTime(vo.stat_time)+"'";
			updateData(conn,sql);
			freeConnection(conn);
		}catch(Exception ex){
			ex.printStackTrace();
		}
	}

	public int validData2(List<VoteValueVo2> lvo,String user_code) {
		try{
			if(lvo == null || lvo.size() == 0){
				return 0;
			}
			Connection conn = getConnect();
			for(int i =0;i< lvo.size();i++){
				VoteValueVo2 vo = lvo.get(i);
				String sql = "update VOTE_VALUE2 " +
						"	set valid = 1, " +
						"	valid_user_code = '"+user_code+"',"+
						"	valid_time = '"+DateTools.dtsWithTime(new Date())+"' " +
						"	where vote_id = "+vo.vote_id+
						"	and dispense_id = "+ vo.organization_id +
						"	and employee_id = "+vo.employee_id+
						"	and frq_code = '"+vo.frq_code.trim()+"'" +
						"	and stat_time = '"+DateTools.dtsWithTime(vo.stat_time)+"'";
				int x = updateData(conn,sql);
				if(x == 0){
					return 0;
				}
			}
			freeConnection(conn);
			return 1;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;
	}

	public List<VoteValueVo> searchAllDataByOperateIdAndDate(int operate_id,
			String frq_code, String year, int sel) {
		try{
			String sql = "";
			if(frq_code.equals("0001")){//年
				sql = "select vote_vs_operate.vote_id,vote_dict_operate.name,vote_vs_operate.operate_id," +
						"	vote_vs_operate.frq_code,vote_value_operate.score,'"+year+"-1-1' as stat_time," +
						"	vote_value_operate.valid,vote_value_operate.valid_time,vote_value_operate.valid_user_code " +
						"	from vote_value_operate right join vote_vs_operate " +
						"	on vote_value_operate.vote_id = vote_vs_operate.vote_id  " +
						"	and vote_vs_operate.operate_id = vote_value_operate.operate_id"+
						"	and vote_value_operate.frq_code = '"+frq_code+"'"+
						"	and year(vote_value_operate.stat_time) = " +year+
						"	and month(vote_value_operate.stat_time) = " +1+
						"	and day(vote_value_operate.stat_time) = " +1+
						"	left join vote_dict_operate" +
						"	on vote_vs_operate.vote_id = vote_dict_operate.id" +
						"	where vote_vs_operate.operate_id = "+operate_id +
						"	and vote_vs_operate.frq_code = '"+frq_code+"'";
			}else if(frq_code.equals("0002")){//半年
				if(sel == 0){//上半年
					sql = "select vote_vs_operate.vote_id,vote_dict_operate.name,vote_vs_operate.operate_id," +
					"	vote_vs_operate.frq_code,vote_value_operate.score,'"+year+"-1-1' as stat_time ,vote_value_operate.valid,vote_value_operate.valid_time,vote_value_operate.valid_user_code" +
					"	from vote_value_operate right join vote_vs_operate " +
					"	on vote_value_operate.vote_id = vote_vs_operate.vote_id  " +
					"	and vote_value_operate.frq_code = '"+frq_code+"'"+
					"	and vote_vs_operate.operate_id = vote_value_operate.operate_id"+
					"	and year(vote_value_operate.stat_time) = " +year+
					"	and month(vote_value_operate.stat_time) = 1"+
					"	left join vote_dict_operate" +
					"	on vote_vs_operate.vote_id = vote_dict_operate.id" +
					"	where vote_vs_operate.operate_id = "+operate_id +
					"	and vote_vs_operate.frq_code = '"+frq_code+"'";
				}else{//下半年
					sql = "select vote_vs_operate.vote_id,vote_dict_operate.name,vote_vs_operate.operate_id," +
					"	vote_vs_operate.frq_code,vote_value_operate.score,'"+year+"-7-1' as stat_time ,vote_value_operate.valid,vote_value_operate.valid_time,vote_value_operate.valid_user_code" +
					"	from vote_value_operate right join vote_vs_operate " +
					"	on vote_value_operate.vote_id = vote_vs_operate.vote_id  " +
					"	and vote_value_operate.frq_code = '"+frq_code+"'"+
					"	and vote_vs_operate.operate_id = vote_value_operate.operate_id"+
					"	and year(vote_value_operate.stat_time) = " +year+
					"	and month(vote_value_operate.stat_time) = 7"+
					"	left join vote_dict_operate" +
					"	on vote_vs_operate.vote_id = vote_dict_operate.id" +
					"	where vote_vs_operate.operate_id = "+operate_id +
					"	and vote_vs_operate.frq_code = '"+frq_code+"'";
				}
			}else if(frq_code.equals("0003")){//季度
				if(sel == 0){//1
					sql = "select vote_vs_operate.vote_id,vote_dict_operate.name,vote_vs_operate.operate_id," +
					"	vote_vs_operate.frq_code,vote_value_operate.score,'"+year+"-1-1' as stat_time ,vote_value_operate.valid,vote_value_operate.valid_time,vote_value_operate.valid_user_code" +
					"	from vote_value_operate right join vote_vs_operate " +
					"	on vote_value_operate.vote_id = vote_vs_operate.vote_id  " +
					"	and vote_value_operate.frq_code = '"+frq_code+"'"+
					"	and vote_vs_operate.operate_id = vote_value_operate.operate_id"+
					"	and year(vote_value_operate.stat_time) = " +year+
					"	and month(vote_value_operate.stat_time) = 1"+
					"	left join vote_dict_operate" +
					"	on vote_vs_operate.vote_id = vote_dict_operate.id" +
					"	where vote_vs_operate.operate_id = "+operate_id +
					"	and vote_vs_operate.frq_code = '"+frq_code+"'";
				}else if(sel == 1){//2
					sql = "select vote_vs_operate.vote_id,vote_dict_operate.name,vote_vs_operate.operate_id," +
					"	vote_vs_operate.frq_code,vote_value_operate.score,'"+year+"-4-1' as stat_time ,vote_value_operate.valid,vote_value_operate.valid_time,vote_value_operate.valid_user_code" +
					"	from vote_value_operate right join vote_vs_operate " +
					"	on vote_value_operate.vote_id = vote_vs_operate.vote_id  " +
					"	and vote_value_operate.frq_code = '"+frq_code+"'"+
					"	and vote_vs_operate.operate_id = vote_value_operate.operate_id"+
					"	and year(vote_value_operate.stat_time) = " +year+
					"	and month(vote_value_operate.stat_time) = 4"+
					"	left join vote_dict_operate" +
					"	on vote_vs_operate.vote_id = vote_dict_operate.id" +
					"	where vote_vs_operate.operate_id = "+operate_id +
					"	and vote_vs_operate.frq_code = '"+frq_code+"'";
				}else if(sel == 2){//3
					sql = "select vote_vs_operate.vote_id,vote_dict_operate.name,vote_vs_operate.operate_id," +
					"	vote_vs_operate.frq_code,vote_value_operate.score,'"+year+"-7-1' as stat_time ,vote_value_operate.valid,vote_value_operate.valid_time,vote_value_operate.valid_user_code" +
					"	from vote_value_operate right join vote_vs_operate " +
					"	on vote_value_operate.vote_id = vote_vs_operate.vote_id  " +
					"	and vote_value_operate.frq_code = '"+frq_code+"'"+
					"	and vote_vs_operate.operate_id = vote_value_operate.operate_id"+
					"	and year(vote_value_operate.stat_time) = " +year+
					"	and month(vote_value_operate.stat_time) = 7"+
					"	left join vote_dict_operate" +
					"	on vote_vs_operate.vote_id = vote_dict_operate.id" +
					"	where vote_vs_operate.operate_id = "+operate_id +
					"	and vote_vs_operate.frq_code = '"+frq_code+"'";
				}else{//4
					sql = "select vote_vs_operate.vote_id,vote_dict_operate.name,vote_vs_operate.operate_id," +
					"	vote_vs_operate.frq_code,vote_value_operate.score,'"+year+"-10-1' as stat_time,vote_value_operate.valid,vote_value_operate.valid_time,vote_value_operate.valid_user_code " +
					"	from vote_value_operate right join vote_vs_operate " +
					"	on vote_value_operate.vote_id = vote_vs_operate.vote_id  " +
					"	and vote_value_operate.frq_code = '"+frq_code+"'"+
					"	and vote_vs_operate.operate_id = vote_value_operate.operate_id"+
					"	and year(vote_value_operate.stat_time) = " +year+
					"	and month(vote_value_operate.stat_time) = 10"+
					"	left join vote_dict_operate" +
					"	on vote_vs_operate.vote_id = vote_dict_operate.id" +
					"	where vote_vs_operate.operate_id = "+operate_id +
					"	and vote_vs_operate.frq_code = '"+frq_code+"'";
				}
			}else{//月
				sql = "select vote_vs_operate.vote_id,vote_dict_operate.name,vote_vs_operate.operate_id," +
				"	vote_vs_operate.frq_code,vote_value_operate.score,'"+year+"-"+(sel+1)+"-1' as stat_time ,vote_value_operate.valid,vote_value_operate.valid_time,vote_value_operate.valid_user_code" +
				"	from vote_value_operate right join vote_vs_operate " +
				"	on vote_value_operate.vote_id = vote_vs_operate.vote_id  " +
				"	and vote_value_operate.frq_code = '"+frq_code+"'"+
				"	and vote_vs_operate.operate_id = vote_value_operate.operate_id"+
				"	and year(vote_value_operate.stat_time) = " +year+
				"	and month(vote_value_operate.stat_time) = " +(sel+1)+
				"	left join vote_dict_operate" +
				"	on vote_vs_operate.vote_id = vote_dict_operate.id" +
				"	where vote_vs_operate.operate_id = "+operate_id +
				"	and vote_vs_operate.frq_code = '"+frq_code+"'";
			}
			
			List<VoteValueVo> l = new ArrayList<VoteValueVo>();
			Connection conn = getConnect();
			ResultSet rs = queryData(conn,sql);
			while(rs.next()){
				VoteValueVo vo = new VoteValueVo();
				vo.organization_id = rs.getInt("operate_id");
				vo.score = rs.getBigDecimal("score")!=null?rs.getBigDecimal("score").toString():"";
				vo.stat_time = rs.getDate("stat_time");
				if(vo.stat_time != null){
					vo.stat_time_str = DateTools.dtsWithTime(vo.stat_time);
				}
				if(rs.getDate("valid_time") != null){
					vo.valid_time = DateTools.dtsJustDate(rs.getDate("valid_time"));
				}
				vo.valid_user_code = rs.getString("valid_user_code");
				vo.valid = rs.getInt("valid");
				vo.frq_code = rs.getString("frq_code");
				vo.vote_id = rs.getInt("vote_id");
				vo.vote_name = rs.getString("name");
				l.add(vo);
			}
			freeConnection(conn);
			return l;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;

	}

	public int deleteDataOperate(VoteValueVo vo) {
		try{
			System.out.println("删除:   "+vo);
			Connection conn = getConnect();
			String sql = "delete vote_value_operate " +
					"	where vote_id = "+vo.vote_id+" and operate_id = "+ vo.organization_id +
					"	and frq_code = '"+vo.frq_code.trim()+"' and stat_time = '"+DateTools.dtsWithTime(vo.stat_time)+"'";
			int x = updateData(conn,sql);
			freeConnection(conn);
			return x;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;
	
	}

	public int insertDataOperate(VoteValueVo vo) {
		try{
			String sql = "insert into vote_value_operate" +
			"	(vote_id,operate_id,score,frq_code,create_time,modify_time,stat_time)" +
			"	values("+vo.vote_id+","+vo.organization_id+","+vo.score+"," +
			"	'"+vo.frq_code.trim()+"','"+DateTools.dtsWithTime(new Date())+"'," +
			"	'"+DateTools.dtsWithTime(new Date())+"','"+DateTools.dtsWithTime(vo.stat_time)+"')";
			Connection conn = getConnect();
			int x =  updateData(conn,sql);
			freeConnection(conn);
			return x;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;
	
	}

	public int updateValueOperate(VoteValueVo vo) {
		try{
			System.out.println("修改  vo	"+vo);
			Connection conn = getConnect();
			String sql = "update vote_value_operate " +
					"	set score = " + vo.score+", modify_time = '"+DateTools.dtsWithTime(new Date())+"' " +
					"	where vote_id = "+vo.vote_id+" and operate_id = "+ vo.organization_id +
					"	and frq_code = '"+vo.frq_code.trim()+"' and stat_time = '"+DateTools.dtsWithTime(vo.stat_time)+"'";
			int x = updateData(conn,sql);
			freeConnection(conn);
			return x;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;
	
	}

	public void validDataOperate(VoteValueVo vo) {
		try{
			System.out.println("修改  vo	"+vo);
			Connection conn = getConnect();
			String sql = "update vote_value_operate " +
					"	set valid = 1, " +
					"	valid_time = '"+DateTools.dtsWithTime(new Date())+"', " +
					"	valid_user_code = '"+vo.valid_user_code+"' " +
					"	where vote_id = "+vo.vote_id+
					"	and operate_id = "+ vo.organization_id +
					"	and frq_code = '"+vo.frq_code.trim()+"'" +
					"	and stat_time = '"+DateTools.dtsWithTime(vo.stat_time)+"'";
			updateData(conn,sql);
			freeConnection(conn);
		}catch(Exception ex){
			ex.printStackTrace();
		}
	
	}

	public int deleteDataAdmin(VoteValueVo vo) {
		try{
			System.out.println("删除:   "+vo);
			Connection conn = getConnect();
			String sql = "delete vote_value_ADMIN " +
					"	where vote_id = "+vo.vote_id+" and admin_id = "+ vo.organization_id +
					"	and frq_code = '"+vo.frq_code.trim()+"' and stat_time = '"+DateTools.dtsWithTime(vo.stat_time)+"'";
			int x = updateData(conn,sql);
			freeConnection(conn);
			return x;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;
	
	}

	public int insertDataAdmin(VoteValueVo vo) {
		try{
			String sql = "insert into vote_value_ADMIN" +
			"	(vote_id,admin_id,score,frq_code,create_time,modify_time,stat_time)" +
			"	values("+vo.vote_id+","+vo.organization_id+","+vo.score+"," +
			"	'"+vo.frq_code.trim()+"','"+DateTools.dtsWithTime(new Date())+"'," +
			"	'"+DateTools.dtsWithTime(new Date())+"','"+DateTools.dtsWithTime(vo.stat_time)+"')";
			Connection conn = getConnect();
			int x =  updateData(conn,sql);
			freeConnection(conn);
			return x;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;
	}

	public int updateValueAdmin(VoteValueVo vo) {
		try{
			System.out.println("修改  vo	"+vo);
			Connection conn = getConnect();
			String sql = "update vote_value_ADMIN " +
					"	set score = " + vo.score+", modify_time = '"+DateTools.dtsWithTime(new Date())+"' " +
					"	where vote_id = "+vo.vote_id+" and admin_id = "+ vo.organization_id +
					"	and frq_code = '"+vo.frq_code.trim()+"' and stat_time = '"+DateTools.dtsWithTime(vo.stat_time)+"'";
			int x = updateData(conn,sql);
			freeConnection(conn);
			return x;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return 0;
	}

	public void validDataAdmin(VoteValueVo vo) {
		try{
			System.out.println("修改  vo	"+vo);
			Connection conn = getConnect();
			String sql = "update VOTE_VALUE_ADMIN " +
					"	set valid = 1, " +
					"	valid_time = '"+DateTools.dtsWithTime(new Date())+"', " +
					"	valid_user_code = '"+vo.valid_user_code+"' " +
					"	where vote_id = "+vo.vote_id+
					"	and admin_id = "+ vo.organization_id +
					"	and frq_code = '"+vo.frq_code.trim()+"'" +
					"	and stat_time = '"+DateTools.dtsWithTime(vo.stat_time)+"'";
			updateData(conn,sql);
			freeConnection(conn);
		}catch(Exception ex){
			ex.printStackTrace();
		}
	}

	public List<VoteValueVo> searchAllDataByAdminIdAndDate(int admin_id,
			String frq_code, String year, int sel) {
		try{
			String sql = "";
			if(frq_code.equals("0001")){//年
				sql = "select vote_vs_admin.vote_id,vote_dict_admin.name,vote_vs_admin.admin_id," +
						"	vote_vs_admin.frq_code,vote_value_admin.score,'"+year+"-1-1' as stat_time," +
						"	vote_value_admin.valid,vote_value_admin.valid_time,vote_value_admin.valid_user_code " +
						"	from vote_value_admin right join vote_vs_admin " +
						"	on vote_value_admin.vote_id = vote_vs_admin.vote_id  " +
						"	and vote_value_admin.frq_code = '"+frq_code+"'"+
						"	and vote_vs_admin.admin_id = vote_value_admin.admin_id"+
						"	and year(vote_value_admin.stat_time) = " +year+
						"	and month(vote_value_admin.stat_time) = " +1+
						"	and day(vote_value_admin.stat_time) = " +1+
						"	left join vote_dict_admin" +
						"	on vote_vs_admin.vote_id = vote_dict_admin.id" +
						"	where vote_vs_admin.admin_id = "+admin_id +
						"	and vote_vs_admin.frq_code = '"+frq_code+"'";
			}else if(frq_code.equals("0002")){//半年
				if(sel == 0){//上半年
					sql = "select vote_vs_admin.vote_id,vote_dict_admin.name,vote_vs_admin.admin_id," +
					"	vote_vs_admin.frq_code,vote_value_admin.score,'"+year+"-1-1' as stat_time ,vote_value_admin.valid,vote_value_admin.valid_time,vote_value_admin.valid_user_code" +
					"	from vote_value_admin right join vote_vs_admin " +
					"	on vote_value_admin.vote_id = vote_vs_admin.vote_id  " +
					"	and vote_value_admin.frq_code = '"+frq_code+"'"+
					"	and vote_vs_admin.admin_id = vote_value_admin.admin_id"+
					"	and year(vote_value_admin.stat_time) = " +year+
					"	and month(vote_value_admin.stat_time) = 1"+
					"	left join vote_dict_admin" +
					"	on vote_vs_admin.vote_id = vote_dict_admin.id" +
					"	where vote_vs_admin.admin_id = "+admin_id +
					"	and vote_vs_admin.frq_code = '"+frq_code+"'";
				}else{//下半年
					sql = "select vote_vs_admin.vote_id,vote_dict_admin.name,vote_vs_admin.admin_id," +
					"	vote_vs_admin.frq_code,vote_value_admin.score,'"+year+"-7-1' as stat_time ,vote_value_admin.valid,vote_value_admin.valid_time,vote_value_admin.valid_user_code" +
					"	from vote_value_admin right join vote_vs_admin " +
					"	on vote_value_admin.vote_id = vote_vs_admin.vote_id  " +
					"	and vote_value_admin.frq_code = '"+frq_code+"'"+
					"	and vote_vs_admin.admin_id = vote_value_admin.admin_id"+
					"	and year(vote_value_admin.stat_time) = " +year+
					"	and month(vote_value_admin.stat_time) = 7"+
					"	left join vote_dict_admin" +
					"	on vote_vs_admin.vote_id = vote_dict_admin.id" +
					"	where vote_vs_admin.admin_id = "+admin_id +
					"	and vote_vs_admin.frq_code = '"+frq_code+"'";
				}
			}else if(frq_code.equals("0003")){//季度
				if(sel == 0){//1
					sql = "select vote_vs_admin.vote_id,vote_dict_admin.name,vote_vs_admin.admin_id," +
					"	vote_vs_admin.frq_code,vote_value_admin.score,'"+year+"-1-1' as stat_time ,vote_value_admin.valid,vote_value_admin.valid_time,vote_value_admin.valid_user_code" +
					"	from vote_value_admin right join vote_vs_admin " +
					"	on vote_value_admin.vote_id = vote_vs_admin.vote_id  " +
					"	and vote_value_admin.frq_code = '"+frq_code+"'"+
					"	and vote_vs_admin.admin_id = vote_value_admin.admin_id"+
					"	and year(vote_value_admin.stat_time) = " +year+
					"	and month(vote_value_admin.stat_time) = 1"+
					"	left join vote_dict_admin" +
					"	on vote_vs_admin.vote_id = vote_dict_admin.id" +
					"	where vote_vs_admin.admin_id = "+admin_id +
					"	and vote_vs_admin.frq_code = '"+frq_code+"'";
				}else if(sel == 1){//2
					sql = "select vote_vs_admin.vote_id,vote_dict_admin.name,vote_vs_admin.admin_id," +
					"	vote_vs_admin.frq_code,vote_value_admin.score,'"+year+"-4-1' as stat_time ,vote_value_admin.valid,vote_value_admin.valid_time,vote_value_admin.valid_user_code" +
					"	from vote_value_admin right join vote_vs_admin " +
					"	on vote_value_admin.vote_id = vote_vs_admin.vote_id  " +
					"	and vote_value_admin.frq_code = '"+frq_code+"'"+
					"	and vote_vs_admin.admin_id = vote_value_admin.admin_id"+
					"	and year(vote_value_admin.stat_time) = " +year+
					"	and month(vote_value_admin.stat_time) = 4"+
					"	left join vote_dict_admin" +
					"	on vote_vs_admin.vote_id = vote_dict_admin.id" +
					"	where vote_vs_admin.admin_id = "+admin_id +
					"	and vote_vs_admin.frq_code = '"+frq_code+"'";
				}else if(sel == 2){//3
					sql = "select vote_vs_admin.vote_id,vote_dict_admin.name,vote_vs_admin.admin_id," +
					"	vote_vs_admin.frq_code,vote_value_admin.score,'"+year+"-7-1' as stat_time ,vote_value_admin.valid,vote_value_admin.valid_time,vote_value_admin.valid_user_code" +
					"	from vote_value_admin right join vote_vs_admin " +
					"	on vote_value_admin.vote_id = vote_vs_admin.vote_id  " +
					"	and vote_value_admin.frq_code = '"+frq_code+"'"+
					"	and vote_vs_admin.admin_id = vote_value_admin.admin_id"+
					"	and year(vote_value_admin.stat_time) = " +year+
					"	and month(vote_value_admin.stat_time) = 7"+
					"	left join vote_dict_admin" +
					"	on vote_vs_admin.vote_id = vote_dict_admin.id" +
					"	where vote_vs_admin.admin_id = "+admin_id +
					"	and vote_vs_admin.frq_code = '"+frq_code+"'";
				}else{//4
					sql = "select vote_vs_admin.vote_id,vote_dict_admin.name,vote_vs_admin.admin_id," +
					"	vote_vs_admin.frq_code,vote_value_admin.score,'"+year+"-10-1' as stat_time,vote_value_admin.valid,vote_value_admin.valid_time,vote_value_admin.valid_user_code " +
					"	from vote_value_admin right join vote_vs_admin " +
					"	on vote_value_admin.vote_id = vote_vs_admin.vote_id  " +
					"	and vote_value_admin.frq_code = '"+frq_code+"'"+
					"	and vote_vs_admin.admin_id = vote_value_admin.admin_id"+
					"	and year(vote_value_admin.stat_time) = " +year+
					"	and month(vote_value_admin.stat_time) = 10"+
					"	left join vote_dict_admin" +
					"	on vote_vs_admin.vote_id = vote_dict_admin.id" +
					"	where vote_vs_admin.admin_id = "+admin_id +
					"	and vote_vs_admin.frq_code = '"+frq_code+"'";
				}
			}else{//月
				sql = "select vote_vs_admin.vote_id,vote_dict_admin.name,vote_vs_admin.admin_id," +
				"	vote_vs_admin.frq_code,vote_value_admin.score,'"+year+"-"+(sel+1)+"-1' as stat_time ,vote_value_admin.valid,vote_value_admin.valid_time,vote_value_admin.valid_user_code" +
				"	from vote_value_admin right join vote_vs_admin " +
				"	on vote_value_admin.vote_id = vote_vs_admin.vote_id  " +
				"	and vote_value_admin.frq_code = '"+frq_code+"'"+
				"	and vote_vs_admin.admin_id = vote_value_admin.admin_id"+
				"	and year(vote_value_admin.stat_time) = " +year+
				"	and month(vote_value_admin.stat_time) = " +(sel+1)+
				"	left join vote_dict_admin" +
				"	on vote_vs_admin.vote_id = vote_dict_admin.id" +
				"	where vote_vs_admin.admin_id = "+admin_id +
				"	and vote_vs_admin.frq_code = '"+frq_code+"'";
			}
			
			List<VoteValueVo> l = new ArrayList<VoteValueVo>();
			Connection conn = getConnect();
			ResultSet rs = queryData(conn,sql);
			while(rs.next()){
				VoteValueVo vo = new VoteValueVo();
				vo.organization_id = rs.getInt("admin_id");
				vo.score = rs.getBigDecimal("score")!=null?rs.getBigDecimal("score").toString():"";
				vo.stat_time = rs.getDate("stat_time");
				if(vo.stat_time != null){
					vo.stat_time_str = DateTools.dtsWithTime(vo.stat_time);
				}
				if(rs.getDate("valid_time") != null){
					vo.valid_time = DateTools.dtsJustDate(rs.getDate("valid_time"));
				}
				vo.valid_user_code = rs.getString("valid_user_code");
				vo.valid = rs.getInt("valid");
				vo.frq_code = rs.getString("frq_code");
				vo.vote_id = rs.getInt("vote_id");
				vo.vote_name = rs.getString("name");
				l.add(vo);
			}
			freeConnection(conn);
			return l;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;

	}

	public List<JiangChengDictVo> searchJiangchengValue(int organization_id, String stat_time_str) {
		try{
			String sql = "select * from JIANGCHENG_VALUE " +
				" where stat_time = '" +stat_time_str+"' " +
				" and organization_id = "+organization_id+"  order by jiangcheng_id";
			Connection conn = getConnect();
			ResultSet rs = queryData(conn, sql);
			List<JiangChengDictVo> lst  = new ArrayList<JiangChengDictVo>();
			while(rs.next())
			{
				JiangChengDictVo vo = new JiangChengDictVo();
				vo.id = rs.getInt("jiangcheng_id");
				vo.jiangcheng_type = rs.getInt("jiangcheng_type");
				vo.organization_id = rs.getInt("organization_id");
				vo.value = rs.getBigDecimal("value").toString();
				vo.gonghao = rs.getString("gonghao");
				vo.xingming = rs.getString("xingming");
				vo.stat_time_str = DateTools.dtsJustDate(rs.getDate("stat_time"));
				lst.add(vo);
			}
			freeConnection(conn);
			return lst;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}

	private void deleteJiangchengValue(OrganizationVo vo,String stat_time_str,Connection conn)
	{
		try
		{
			if(conn == null)
			{
				conn = getConnect();
			}
			String sql = "delete JIANGCHENG_VALUE where organization_id = "+vo.id+" " +
					" and stat_time = '"+stat_time_str+"'";
			updateData(conn, sql);
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
	}
	public void updateJiangchengValue(OrganizationVo ovo,String stat_time_str,List<JiangChengDictVo> lst) {
		try{
			Connection conn = getConnect();
			deleteJiangchengValue(ovo,stat_time_str,conn);
			for(int i = 0;i<lst.size();i++)
			{
				JiangChengDictVo vo = lst.get(i);
				String gonghao ="";
				if(gonghao == null)
				{
					gonghao = "NULL";
				}
				else
				{
					gonghao = "'"+vo.gonghao+"'";
				}
				String xingming = "";
				if(xingming == null)
				{
					xingming = "NULL";
				}
				else
				{
					xingming = "'"+vo.xingming+"'";
				}
				String sql = "insert into JIANGCHENG_VALUE"+
					" (jiangcheng_id,organization_id,value,jiangcheng_type,stat_time,gonghao,xingming,xishu)" +
					" values("+vo.id+","+ovo.id+","+vo.value+","+vo.jiangcheng_type+"," +
					" '"+stat_time_str+"',"+gonghao+"," +
					" "+xingming+","+vo.xishu+")";
				updateData(conn, sql);
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}
	}

	public List<JiangChengDictVo> searchLingdaoValue(int organization_id,
			String stat_time_str) {
		try{
			String sql = "select * from LINGDAO_VALUE " +
				" where stat_time = '" +stat_time_str+"' " +
				" and organization_id = "+organization_id+"  order by xingming";
			Connection conn = getConnect();
			ResultSet rs = queryData(conn, sql);
			List<JiangChengDictVo> lst  = new ArrayList<JiangChengDictVo>();
			while(rs.next())
			{
				JiangChengDictVo vo = new JiangChengDictVo();
				vo.gonghao = rs.getString("gonghao");
				vo.xingming = rs.getString("xingming");
				vo.organization_id = rs.getInt("organization_id");
				if(rs.getBigDecimal("xishu") == null)
				{
					vo.xishu = "1";
				}
				else
				{
					vo.xishu = rs.getBigDecimal("xishu").toString();
				}
				vo.jibie = rs.getString("jibie");
				vo.jibie_fen = rs.getBigDecimal("jibie_fen")!=null?rs.getBigDecimal("jibie_fen").toString():"0";
				vo.jiangli = rs.getBigDecimal("jiangli")!=null?rs.getBigDecimal("jiangli").toString():"0";
				vo.chengfa = rs.getBigDecimal("chengfa")!=null?rs.getBigDecimal("chengfa").toString():"0";
				vo.bufa = rs.getBigDecimal("bufa")!=null?rs.getBigDecimal("bufa").toString():"0";
				vo.value = rs.getBigDecimal("value")!=null?rs.getBigDecimal("value").toString():"0";
				vo.stat_time_str = DateTools.dtsJustDate(rs.getDate("stat_time"));
				lst.add(vo);
			}
			freeConnection(conn);
			return lst;
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}
	private void deleteLingdaoValue(OrganizationVo vo,String stat_time_str,Connection conn)
	{
		try
		{
			if(conn == null)
			{
				conn = getConnect();
			}
			String sql = "delete LINGDAO_VALUE where organization_id = "+vo.id+" " +
					" and stat_time = '"+stat_time_str+"'";
			updateData(conn, sql);
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
	}
	public void updateLingdaoValue(OrganizationVo ovo, String stat_time_str,
			List<JiangChengDictVo> lst) {
		try{
			Connection conn = getConnect();
			deleteLingdaoValue(ovo,stat_time_str,conn);
			for(int i = 0;i<lst.size();i++)
			{
				JiangChengDictVo vo = lst.get(i);
				String sql = "insert into LINGDAO_VALUE"+
					" (organization_id,stat_time,gonghao,xingming," +
					" jiangli,chengfa,bufa,jibie,jibie_fen,xishu)" +
					" values("+ovo.id+",'"+stat_time_str+"','"+vo.gonghao+"','"+vo.xingming+"'," +
					" "+(vo.jiangli!=null?vo.jiangli:"0")+"," +
					(vo.chengfa!=null?vo.chengfa:"0")+"," +
					(vo.bufa!=null?vo.bufa:"0")+"," +
					DataFormater.valueToSQL(vo.jibie)+"," +
					(vo.jibie_fen!=null?vo.jibie_fen:"0")+"," +
					vo.xishu+")";
				updateData(conn, sql);
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}
	}

	public int updateVoteValue(List<VoteValueVo> lst) {
		try
		{
			Connection conn = getConnect();
			for(int i = 0;i<lst.size();i++)
			{
				VoteValueVo vo = lst.get(i);
				if(vo.valid == 1)
				{
					continue;
				}
				String sql = "select * from VOTE_VALUE where vote_id = "+vo.vote_id+
					" and organization_id = "+vo.organization_id+
					" and stat_time = '"+vo.stat_time_str+"'" +
					" and frq_code = '"+vo.frq_code+"'";
				ResultSet rs = queryData(conn, sql);
				if(rs.next())//找到，更新
				{
					sql = "update VOTE_VALUE set score = "+vo.score+", " +
						" xishu="+vo.xishu+""+
						" where vote_id = "+vo.vote_id+
						" and organization_id = "+vo.organization_id+
						" and stat_time = '"+vo.stat_time_str+"'" +
						" and frq_code = '"+vo.frq_code+"'";
				}
				else//插入
				{
					sql = "insert into VOTE_VALUE " +
						"(vote_id,organization_id,score,frq_code,create_time,modify_time,stat_time,xishu)" +
						" values("+vo.vote_id+","+vo.organization_id+","+vo.score+",'"+vo.frq_code+"'," +
						"'"+DateTools.dtsWithTime(new Date())+"','"+DateTools.dtsWithTime(new Date())+"','"+vo.stat_time_str+"',"+vo.xishu+")";
				}
				updateVoteValueMingdan(vo,conn);
				updateData(conn, sql);
			}
			return 1;
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
		return 0;
	}

	private void updateVoteValueMingdan(VoteValueVo vo,Connection conn)
	{
		try
		{
			String sql = "delete VOTE_VALUE_MINGDAN where stat_time = '"+vo.stat_time_str+"'" +
					" and vote_id = "+vo.vote_id+
					" and organization_id ="+vo.organization_id;
			updateData(conn, sql);
			List<VoteValueMingdanVo> l = vo.mingdan_arr;
			if(l == null)
			{
				return;
			}
			for(VoteValueMingdanVo mdVo:l)
			{
				
				sql = "insert into VOTE_VALUE_MINGDAN" +
					"	(stat_time,vote_id,organization_id,gonghao,name,xishu)" +
					"	values('"+vo.stat_time_str+"',"+vo.vote_id+","+
					vo.organization_id+","+
					DataFormater.sqlString(mdVo.gonghao)+"," +
					DataFormater.sqlString(mdVo.name)+","+
					mdVo.xishu+")";
				updateData(conn, sql);
			}
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
	}
	public int loadLastByItem(String frq_code, String year, int sel) {
		try
		{
			Connection conn = getConnect();
			String this_time ="" ;
			String last_time ="";
			if(frq_code.equals("0001"))//年
			{
				this_time = year+"-1-1";
				last_time = (Integer.valueOf(year)-1)+"-1-1";
				
				
			}
			else if(frq_code.equals("0002"))//半年
			{
				if(sel == 0)
				{
					this_time = year+"-1-1";
					last_time = (Integer.valueOf(year)-1)+"-6-1";
				}
				else
				{
					this_time = year+"-6-1";
					last_time = year+"-1-1";
				}
			}
			else if(frq_code.equals("0003"))//季
			{
				if(sel == 0)
				{
					this_time = year+"-1-1";
					last_time = (Integer.valueOf(year)-1)+"-10-1";
				}
				else
				{
					this_time = year+"-"+(sel*3+1)+"-1";
					last_time = year+"-"+((sel-1)*3+1)+"-1";
				}				
			}
			else if(frq_code.equals("0004"))//月
			{
				if(sel == 0)
				{
					this_time = year+"-1-1";
					last_time = (Integer.valueOf(year)-1)+"-12-1";
				}
				else
				{
					this_time = year+"-"+(sel+1)+"-1";
					last_time = year+"-"+(sel)+"-1";
				}
			}
			String sql = "delete VOTE_VALUE"+
				" where frq_code = '"+frq_code+"'" +
				" and stat_time = '"+this_time+"'";
			updateData(conn, sql);				
			
			sql = "insert into VOTE_VALUE(vote_id,organization_id,score,frq_code,create_time,modify_time,stat_time)" +
				" select vote_id,organization_id,score,frq_code,'"+DateTools.dtsWithTime(new Date())+"'," +
				" '"+DateTools.dtsWithTime(new Date())+"','"+this_time+"' from VOTE_VALUE" +
				" where frq_code = '"+frq_code+"'" +
				" and stat_time = '"+last_time+"'";
			updateData(conn, sql);
			
			sql = "	delete VOTE_VALUE_MINGDAN " +
				"	where stat_time = '"+this_time+"'" +
				"	and vote_id in" +
				"	(select vote_id " +
				"	from VOTE_VALUE  " +
				"	where frq_code = '"+frq_code+"'" +
				"	and stat_time = '"+this_time+"')";
			updateData(conn, sql);
			
			sql = "insert into VOTE_VALUE_MINGDAN(stat_time,vote_id,gonghao,name,xishu,organization_id)" +
				" select '"+this_time+"',vote_id,gonghao,name,xishu,organization_id from VOTE_VALUE_MINGDAN" +
				" where stat_time = '"+last_time+"'" +
				"	and vote_id in" +
				"	(select vote_id " +
				"	from VOTE_VALUE  " +
				"	where frq_code = '"+frq_code+"'" +
				"	and stat_time = '"+last_time+"')";
			updateData(conn, sql);
			return 1;
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
		return 0;
	}

	public int loadLastLingdaoValue(String stat_time_str) {
		try
		{
			Connection conn = getConnect();
			String this_time = "";
			String last_time = "";
			if(stat_time_str.split("-")[1].equals("1"))
			{
				this_time = stat_time_str;
				last_time = (Integer.valueOf(stat_time_str.split("-")[0])-1)+"-12-1";
			}
			else
			{
				this_time = stat_time_str;
				last_time = (Integer.valueOf(stat_time_str.split("-")[0]))+"-"+(Integer.valueOf(stat_time_str.split("-")[1])-1)+"-1";
			}
			String sql = "delete LINGDAO_VALUE"+
				" where stat_time = '"+this_time+"'";
			updateData(conn, sql);				
			sql = "insert into LINGDAO_VALUE" +
				" (organization_id,stat_time,gonghao,xingming,jibie,jibie_fen,xishu,jiangli,chengfa,bufa)" +
				" select organization_id,'"+this_time+"',gonghao,xingming,jibie,jibie_fen,xishu,0,0,0" +
				" from LINGDAO_VALUE" +
				" where stat_time = '"+last_time+"'";
			updateData(conn, sql);
			return 1;
		}catch(Exception ex)
		{
			ex.printStackTrace();
		}
		return 0;
	}

	public int loadOrganizationLastLingdaoValue(String stat_time_str,OrganizationVo orgVo) {
		try
		{
			Connection conn = getConnect();
			String this_time = "";
			String last_time = "";
			if(stat_time_str.split("-")[1].equals("1"))
			{
				this_time = stat_time_str;
				last_time = (Integer.valueOf(stat_time_str.split("-")[0])-1)+"-12-1";
			}
			else
			{
				this_time = stat_time_str;
				last_time = (Integer.valueOf(stat_time_str.split("-")[0]))+"-"+(Integer.valueOf(stat_time_str.split("-")[1])-1)+"-1";
			}
			String sql = "delete LINGDAO_VALUE"+
				" where stat_time = '"+this_time+"' and organization_id = "+orgVo.id;
			updateData(conn, sql);				
			sql = "insert into LINGDAO_VALUE" +
					" (organization_id,stat_time,gonghao,xingming,jibie,jibie_fen,xishu,jiangli,chengfa,bufa)" +
				" select organization_id,'"+this_time+"',gonghao,xingming,jibie,jibie_fen,xishu,0,0,0" +
				" from LINGDAO_VALUE" +
				" where stat_time = '"+last_time+"' and organization_id = "+orgVo.id;
			updateData(conn, sql);
			return 1;
		}catch(Exception ex)
		{
			ex.printStackTrace();
		}
		return 0;
	}
}
